import pymysql
class DBTools(object):
    @staticmethod
    def __create_conn(): #变成私有方法
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn

    @staticmethod
    def query_one(sql):
        conn=None
        cursor=None
        res=None
        try:
            conn = DBTools.__create_conn()    #创建链接
            cursor=conn.cursor()  #创建游标
            cursor.execute(sql) #执行用户输入的语句
            res=cursor.fetchone()           #接受查询结果 提取第一个数据
        except Exception as err:
            print(err)
        finally:
            cursor.close()  #关闭游标
            conn.close()     #关闭链接
            return res       # 返回结果

    @staticmethod
    def query_all(sql):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_conn()  # 创建链接
            cursor = conn.cursor()  # 创建游标
            cursor.execute(sql)  # 执行用户输入的语句
            res = cursor.fetchall()  # 接受查询结果 提取所有数据
        except Exception as err:
            print(err)
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭链接
            return res  # 返回结果
    @staticmethod
    def query_many(sql,n):
        conn = None
        cursor = None
        res = None
        try:
            conn = DBTools.__create_conn()  # 创建链接
            cursor = conn.cursor()  # 创建游标
            cursor.execute(sql)  # 执行用户输入的语句
            res = cursor.fetchmany(n)  # 接受查询结果几个结果
        except Exception as err:
            print(err)
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭链接
            return res  # 返回结果
    @staticmethod
    def db_iud(sql,):
        conn = None
        cursor = None
        try:
            conn = DBTools.__create_conn()  # 创建链接
            cursor = conn.cursor()  # 创建游标
            cursor.execute(sql)  # 执行用户输入的语句
            conn.commit() #执行事务
            print("执行成功")
        except Exception as err:
            print(err)
            conn.rollback() #回滚事物
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭链接



if __name__ == '__main__':
    res=DBTools.query_one("select is_delete from bs_user where id=123432;")
    print(res)
    DBTools.db_iud("update t_book set `read`=`read`+1 where id=111;")